﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Qianli.DirSir.Model;
using Qianli.DirSir.Common;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace Qianli.DirSir.DAL
{
    public class DHLUPSFEDEXEMSInfoDAL
    {
        SQLHelper helper = new SQLHelper();

        /// <summary>
        ///获取快递公司信息
        /// </summary>
        /// <returns></returns>
        public List<DHLUPSFEDEXEMSInfo> GetDHLUPSFEDEXEMSInfo()
        {
            string sql = "ShwoDHLUPSFEDEXEMSInfo";

            DataTable table = helper.ExecDataTable_Proc(sql);
            List<DHLUPSFEDEXEMSInfo> list = new List<DHLUPSFEDEXEMSInfo>();
            foreach (DataRow item in table.Rows)
            {
                DHLUPSFEDEXEMSInfo d = new DHLUPSFEDEXEMSInfo();
                d.DHLUPSFEDEXEMSID = Convert.ToInt32(item["DHLUPSFEDEXEMSID"]);
                d.DHLID = item["DHLID"].ToString();
                d.DHLUPSFEDEXEMSName = item["DHLUPSFEDEXEMSName"].ToString();
                d.SurfaceSingleFee = Convert.ToDecimal(item["SurfaceSingleFee"]);
                d.Address = item["Address"].ToString();
                d.Phone = item["Phone"].ToString();
                list.Add(d);              
            }
            return list;

            //string str = "select DHLID,DHLUPSFEDEXEMSName,SurfaceSingleFee,Address,Phone from DHLUPSFEDEXEMSInfo";
            //DataTable table = helper.ExecDataTable(str);
            //List<DHLUPSFEDEXEMSInfo> list = new List<DHLUPSFEDEXEMSInfo>();
            //foreach (DataRow item in table.Rows)
            //{
            //    DHLUPSFEDEXEMSInfo d = new DHLUPSFEDEXEMSInfo();
            //    d.DHLID = item["DHLID"].ToString();
            //    d.DHLUPSFEDEXEMSName = item["DHLUPSFEDEXEMSName"].ToString();
            //    d.SurfaceSingleFee = Convert.ToDecimal(item["SurfaceSingleFee"]);
            //    d.Address = item["Address"].ToString();
            //    d.Phone = item["Phone"].ToString();
            //    list.Add(d);
            //}
            //return list;
        }
        /// <summary>
        /// 添加快递公司信息
        /// </summary>
        /// <param name="dh"></param>
        /// <returns></returns>
        //public int AddDHLUPSFEDEXEMSInfo(DHLUPSFEDEXEMSInfo dh)
        //{
        //    StringBuilder builder = new StringBuilder();
        //    builder.Append("insert into DHLUPSFEDEXEMSInfo(DHLID,DHLUPSFEDEXEMSName,SurfaceSingleFee,Address,Phone) values(@DHLID,@DHLUPSFEDEXEMSName,@SurfaceSingleFee,@Address,@Phone)");

        //    SqlParameter[] paramater ={ 
        //        new SqlParameter("@DHLID",SqlDbType.VarChar,30),
        //        new SqlParameter("@DHLUPSFEDEXEMSName",SqlDbType.VarChar,30),
        //        new SqlParameter("@SurfaceSingleFee",SqlDbType.VarChar,30),
        //        new SqlParameter("@Address",SqlDbType.VarChar,30),
        //        new SqlParameter("@Phone",SqlDbType.VarChar,30)
                    
        //    };
        //    paramater[0].Value = dh.DHLID;
        //    paramater[1].Value = dh.DHLUPSFEDEXEMSName;
        //    paramater[2].Value = dh.SurfaceSingleFee;
        //    paramater[3].Value = dh.Address;
        //    paramater[4].Value = dh.Phone;

        //    int i = helper.ExecNonquery(builder.ToString(), paramater);
        //    return i;
        //}


        /// <summary>
        /// 添加快递公司信息
        /// </summary>
        /// <param name="dh"></param>
        /// <returns></returns>
        public int AddDHLUPSFEDEXEMSInfo(DHLUPSFEDEXEMSInfo dh)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("Proc_AddDHLUPSFEDEXEMSInfo");

            SqlParameter[] paramater ={ 
                new SqlParameter("@DHLID",SqlDbType.VarChar,30),
                new SqlParameter("@DHLUPSFEDEXEMSName",SqlDbType.VarChar,30),
                new SqlParameter("@SurfaceSingleFee",SqlDbType.VarChar,30),
                new SqlParameter("@Address",SqlDbType.VarChar,30),
                new SqlParameter("@Phone",SqlDbType.VarChar,30)
                    
            };
            paramater[0].Value = dh.DHLID;
            paramater[1].Value = dh.DHLUPSFEDEXEMSName;
            paramater[2].Value = dh.SurfaceSingleFee;
            paramater[3].Value = dh.Address;
            paramater[4].Value = dh.Phone;

            int i = helper.ExecNonquery_Proc( builder.ToString(), paramater);
            return i;
        }
        /// <summary>
        /// 根据快递编号删除快递公司
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelDHLUPSFEDEXEMSInfo(int id)
        {
            string sql = "delete from DHLUPSFEDEXEMSInfo where DHLUPSFEDEXEMSID=" + id + "";
            return helper.ExecNonquery(sql);            
        }
        /// <summary>
        /// 根据id搜索快递表信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DHLUPSFEDEXEMSInfo getDHLUPSFEDEXEMSInfoByid(int id)
        {
            string sql = "GetDHLUPSFEDEXEMSInfoByID";
            DHLUPSFEDEXEMSInfo d = new DHLUPSFEDEXEMSInfo();
            SqlParameter[] parameter ={
                         new SqlParameter("@id", SqlDbType.Int,4)     };
            parameter[0].Value = id;

            DataTable dt = helper.ExecDataTable_Proc(sql,parameter);
            d.DHLUPSFEDEXEMSID = Convert.ToInt32(dt.Rows[0]["DHLUPSFEDEXEMSID"]);
            d.DHLID  = dt.Rows[0]["DHLID"].ToString();
            d.DHLUPSFEDEXEMSName = dt.Rows[0]["DHLUPSFEDEXEMSName"].ToString();
            d.SurfaceSingleFee = Convert.ToDecimal(dt.Rows[0]["SurfaceSingleFee"]);
            d.Address = dt.Rows[0]["Address"].ToString();
            d.Phone = dt.Rows[0]["Phone"].ToString();
            return d;        

        }
        /// <summary>
        /// 修改快递表信息
        /// </summary>
        /// <param name="dh"></param>
        /// <returns></returns>
        public int UptDHLUPSFEDEXEMSInfo(DHLUPSFEDEXEMSInfo dh)
        {
            string sql=("UptDHLUPSFEDEXEMSInfo");
            //builder.Append("update DHLUPSFEDEXEMSInfo set ");
            //builder.Append(" DHLID=@DHLID,DHLUPSFEDEXEMSName = @DHLUPSFEDEXEMSName,SurfaceSingleFee = @SurfaceSingleFee,Address = @Address,Phone = @Phone where DHLUPSFEDEXEMSID = @DHLUPSFEDEXEMSID");

            SqlParameter[] paramater ={ 
                new SqlParameter("@DHLID",SqlDbType.VarChar,30),
                new SqlParameter("@DHLUPSFEDEXEMSName",SqlDbType.VarChar,30),
                new SqlParameter("@SurfaceSingleFee",SqlDbType.VarChar,30),
                new SqlParameter("@Address",SqlDbType.VarChar,30),
                new SqlParameter("@Phone",SqlDbType.VarChar,30),
                new SqlParameter("@DHLUPSFEDEXEMSID",SqlDbType.Int,4),
                    
            };
            paramater[0].Value = dh.DHLID;
            paramater[1].Value = dh.DHLUPSFEDEXEMSName;
            paramater[2].Value = dh.SurfaceSingleFee;
            paramater[3].Value = dh.Address;
            paramater[4].Value = dh.Phone;
            paramater[5].Value = dh.DHLUPSFEDEXEMSID;

            int i = helper.ExecNonquery_Proc(sql, paramater);
            return i;
        }
        /// <summary>
        /// 根据姓名查询
        /// </summary>
        /// <param name="DHLUPSFEDEXEMSName"></param>
        /// <returns></returns>
        public List<DHLUPSFEDEXEMSInfo> SearchDHLUPSFEDEXEMSInfo(string DHLUPSFEDEXEMSName)
        {
            string sql = "SearchDHLUPSFEDEXEMSInfo";

           
            SqlParameter[] parameter ={
                         new SqlParameter("@DHLUPSFEDEXEMSName", SqlDbType.VarChar,30)};
            parameter[0].Value = "%"+DHLUPSFEDEXEMSName+"%";
            
            List<DHLUPSFEDEXEMSInfo> list = new List<DHLUPSFEDEXEMSInfo>();
            DataTable dt = helper.ExecDataTable_Proc(sql,parameter);
            foreach (DataRow item in dt.Rows)
            {
                DHLUPSFEDEXEMSInfo d = new DHLUPSFEDEXEMSInfo();
                d.DHLUPSFEDEXEMSID = Convert.ToInt32(item["DHLUPSFEDEXEMSID"]);
                d.DHLID = item["DHLID"].ToString();
                d.DHLUPSFEDEXEMSName = item["DHLUPSFEDEXEMSName"].ToString();
                d.SurfaceSingleFee = Convert.ToDecimal(item["SurfaceSingleFee"]);
                d.Address = item["Address"].ToString();
                d.Phone = item["Phone"].ToString();
                list.Add(d);    
            }

            return list;
        }

    }
}
